package com.htht.smartschool.service.maillist;

import java.util.ArrayList;
import java.util.List;

import com.htht.smartschool.common.service.BaseService;
import com.htht.smartschool.model.course.TCourse;
import com.htht.smartschool.model.maillist.MailData;
import com.htht.smartschool.model.maillist.MessagePersons;
import com.htht.smartschool.model.maillist.Persons;
import com.htht.smartschool.model.maillist.StudentUserMaillist;
import com.htht.smartschool.model.maillist.TMaillist;
import com.htht.smartschool.model.student.TStudent;
import com.htht.smartschool.model.teacher.TClass;
import com.htht.smartschool.model.teacher.TTeacher;
import com.htht.smartschool.util.mail.MailUtil;
import com.htht.smartschool.util.system.ResultSetToExcel;

public class MaillistService extends BaseService{
	/***
	 * 家长获取老师通讯录
	 * @param maillist
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MailData> getMaillistByClasscode(TMaillist maillist) {
		String sql ="SELECT DISTINCT tc.classcode,tcso.teachercode,tt.name,tt.phone,tt.cellphone,tcso.coursecode,tcss.coursecontent "
				+"FROM t_course tc ,t_courseorder tcso,t_teacher tt,t_coursesubject tcss "
				+"WHERE  tc.coursecode = tcso.coursecode AND tcso.teachercode=tt.teachercode AND tcss.subjectcode=tc.subjectcode AND tc.classcode = :classcode ORDER BY CONVERT(tt.name USING gbk)";
		List<Object[]> queryList = getDao().queryBySQL(sql,maillist);
//		ResultSetToExcel.coloumItem = "班级编码,教师编码,教师姓名,教师电话1,教师电话2,课程编码,课程名称";
//		ResultSetToExcel.rs = queryList;
		List<TMaillist> list = new ArrayList<TMaillist>();
		String group = "";
		for (int i = 0; i < queryList.size(); i++) {
			Object[] obj = queryList.get(i);
			TMaillist ml = new TMaillist();
			ml.setClasscode(String.valueOf(obj[0]));
			ml.setTeachercode(String.valueOf(obj[1]));
			ml.setName(String.valueOf(obj[2]));
			ml.setPhone(String.valueOf(obj[3]));
			ml.setCellphone(String.valueOf(obj[4]));
			ml.setCoursecode(String.valueOf(obj[5]));
			ml.setCoursecontent(String.valueOf(obj[6]));
			String groupName = MailUtil.converterToFirstSpell(ml.getName()).substring(0,1);
			ml.setGroupName(groupName);
			group += groupName+",";
			list.add(ml);
		}
		String[] groups = group.split(",");
		List<String> groupList = MailUtil.getDistinct(groups);
		List<MailData> result = new ArrayList<MailData>();
		if(!group.equalsIgnoreCase("")){
		for (int i = 0; i < groupList.size(); i++) {
			MailData md = new MailData();
			String groupName = groupList.get(i);
			List<TMaillist> obj = new ArrayList<TMaillist>();
			for (int j = 0; j < list.size(); j++) {
				TMaillist ml = list.get(j);
				if(ml.getGroupName().equalsIgnoreCase(groupName)){
					obj.add(ml);
				}
			}			
			md.setGroup(obj);
			md.setGroupName(groupName);
			result.add(md);
		}
	}
		return result;
	}
	/***
	 * 获取学生和家长的通讯录
	 * @param classcode
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MailData> getStudentMailList(String classcode) {
		String sql = "SELECT sc.studentcode,sc.classcode,sc.name,IFNULL(tc.cellphone,'')  stu,sc.alias,sc.usercode usr FROM (SELECT DISTINCT ts.studentcode"
				+ ",ts.classcode,ts.name,tu.alias,tu.usercode "
				+ " FROM t_student ts,t_user_student tus,t_user tu "
				+ " WHERE ts.classcode = :classcode AND tus.studentcode = ts.studentcode AND tus.usercode = tu.usercode "
				+ " AND tu.usertype = '1')sc LEFT JOIN t_card tc "
				+ " ON sc.studentcode = tc.studentcode ORDER BY CONVERT(sc.name USING gbk) asc";
		TStudent tstudent = new TStudent();
		tstudent.setClasscode(classcode);
		List<Object[]> list = getDao().queryBySQL("select * from ("+sql+")tc",tstudent);
//		ResultSetToExcel.coloumItem = "学生编码,班级编码,学生姓名,学生电话,家长姓名,家长电话";
//		ResultSetToExcel.rs = list;
		List<MailData> result = new ArrayList<MailData>();
		List<StudentUserMaillist> lsitsml = new ArrayList<StudentUserMaillist>();
		String group = "";
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = list.get(i);
			StudentUserMaillist sml = new StudentUserMaillist();
			sml.setStudentcode(String.valueOf(obj[0]));
			sml.setClasscode(String.valueOf(obj[1]));
			sml.setStudentname(String.valueOf(obj[2]));
			sml.setStudentphone(String.valueOf(obj[3]));
			sml.setUsername(String.valueOf(obj[4]));
			sml.setUserphone(String.valueOf(obj[5]));
			String groupname = MailUtil.converterToFirstSpell(sml.getStudentname()).substring(0,1);
			sml.setGroupname(groupname);
			group+=groupname+",";
			lsitsml.add(sml);
		}
		String[] groups = group.split(",");
		List<String> groupList = MailUtil.getDistinct(groups);
		if(!group.equalsIgnoreCase("")){
			for (String groupName : groupList) {
				MailData md = new MailData();
				List<StudentUserMaillist> obj = new ArrayList<StudentUserMaillist>();
				for (int j = 0; j < lsitsml.size(); j++) {
					StudentUserMaillist ml = lsitsml.get(j);
					if(ml.getGroupname().equalsIgnoreCase(groupName)){
						obj.add(ml);
					}
				}
				md.setStudentgroup(obj);
				md.setGroupName(groupName);
				result.add(md);
			}
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public List<MailData> getAllTeacherMailList(String teachercode) {
		String hql = "SELECT tt.teachercode,tt.name,tt.cellphone,tt.phone FROM t_teacher tt WHERE"
				+ " tt.teachercode <> :teachercode AND tt.teachercode <> '' ORDER BY CONVERT(tt.name USING gbk) asc";
		TTeacher tteacher = new TTeacher();
		tteacher.setTeachercode(teachercode);
		List<Object[]> queryList = getDao().queryBySQL(hql,tteacher);
//		ResultSetToExcel.coloumItem = "教师编码,教师名称,教师电话1,教师电话2";
//		ResultSetToExcel.rs = queryList;
		String group = "";
		List<TMaillist> list = new ArrayList<TMaillist>();
		for (Object[] obj : queryList) {
			TMaillist tMaillist = new TMaillist();
			tMaillist.setTeachercode(String.valueOf(obj[0]));
			tMaillist.setName(String.valueOf(obj[1]));
			tMaillist.setCellphone(String.valueOf(obj[2]));
			tMaillist.setPhone(String.valueOf(obj[3]));
			String groupName = MailUtil.converterToFirstSpell(tMaillist.getName()).substring(0,1);
			tMaillist.setGroupName(groupName);
			group += groupName+",";
			list.add(tMaillist);
		}
		String[] groups = group.split(",");
		List<String> groupList = MailUtil.getDistinct(groups);
		List<MailData> result = new ArrayList<MailData>();
		for (int i = 0; i < groupList.size(); i++) {
			MailData md = new MailData();
			String groupName = groupList.get(i);
			List<TMaillist> obj = new ArrayList<TMaillist>();
			for (int j = 0; j < list.size(); j++) {
				TMaillist ml = list.get(j);
				if(ml.getGroupName().equalsIgnoreCase(groupName)){
					obj.add(ml);
				}
			}
			md.setGroup(obj);
			md.setGroupName(groupName);
			result.add(md);
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public MessagePersons getMessagePersons(String teachercode) {
		MessagePersons mp = new MessagePersons();
		List<Persons> personsList = new ArrayList<Persons>();
		List<TClass> classesList = new ArrayList<TClass>();;
		String classessql = "SELECT DISTINCT tc.classcode,tco.teachercode,tcl.grade,tcl.class FROM t_course tc,t_courseorder tco,t_class tcl WHERE tco.teachercode = :teachercode AND tc.classcode = tcl.classcode  AND tc.coursecode = tco.coursecode";
		String personssql = "SELECT GROUP_CONCAT(ts.name) AS students,tu.alias,tu.usercode,tu.headimg "
				+"FROM (SELECT DISTINCT tc.classcode,tco.teachercode,tcl.grade,tcl.class FROM t_course tc,t_courseorder tco,t_class tcl WHERE tco.teachercode = :teachercode AND tc.classcode = tcl.classcode  AND tc.coursecode = tco.coursecode) tc,t_student ts,t_user_student tus,t_user tu "
				+"WHERE tc.classcode = ts.classcode AND ts.studentcode = tus.studentcode AND tus.usercode = tu.usercode "
				+"GROUP BY tu.alias,tu.usercode  ORDER BY CONVERT(tu.alias USING gbk) asc";
		TCourse tcourse = new TCourse();
		tcourse.setTeachercode(teachercode);
		List<Object[]> classes = getDao().queryBySQL(classessql,tcourse);
		for (Object[] obj : classes) {
			TClass tc = new TClass();
			tc.setClasscode(String.valueOf(obj[0]));
			tc.setName(String.valueOf(obj[2])+"年级"+String.valueOf(obj[3])+"班");
			classesList.add(tc);
		}
		List<Object[]> persons = getDao().queryBySQL(personssql,tcourse);
		for (Object[] obj : persons) {
			Persons p = new Persons();
			p.setStudents(String.valueOf(obj[0]));
			p.setUsername(String.valueOf(obj[1]));
			p.setUsercode(String.valueOf(obj[2]));
			p.setHeading(String.valueOf(obj[3]));
			personsList.add(p);
		}
		mp.setClasses(classesList);
		mp.setPersons(personsList);
		return mp;
	}
}
